const { Op } = require('sequelize');
const { 
  User, 
  Order, 
  Product, 
  PointsTransaction, 
  AdView, 
  Invitation,
  sequelize 
} = require('../models');
const logger = require('../utils/logger');

class DashboardService {
  /**
   * 获取实时数据大屏概览
   */
  async getDashboardOverview() {
    try {
      const now = new Date();
      const today = new Date(now.getFullYear(), now.getMonth(), now.getDate());
      const yesterday = new Date(today.getTime() - 24 * 60 * 60 * 1000);
      const lastWeek = new Date(today.getTime() - 7 * 24 * 60 * 60 * 1000);
      const lastMonth = new Date(today.getTime() - 30 * 24 * 60 * 60 * 1000);

      const [
        // 用户统计
        totalUsers,
        todayNewUsers,
        weekNewUsers,
        activeUsers,
        
        // 订单统计
        totalOrders,
        todayOrders,
        weekOrders,
        totalRevenue,
        todayRevenue,
        
        // 积分统计
        totalPointsIssued,
        totalPointsUsed,
        todayPointsIssued,
        
        // 广告统计
        totalAdViews,
        todayAdViews,
        
        // 商品统计
        totalProducts,
        activeProducts,
        lowStockProducts,
        
        // 邀请统计
        totalInvitations,
        todayInvitations
      ] = await Promise.all([
        // 用户相关统计
        User.count(),
        User.count({ where: { createdAt: { [Op.gte]: today } } }),
        User.count({ where: { createdAt: { [Op.gte]: lastWeek } } }),
        User.count({ where: { lastLogin: { [Op.gte]: lastWeek } } }),
        
        // 订单相关统计
        Order.count(),
        Order.count({ where: { createdAt: { [Op.gte]: today } } }),
        Order.count({ where: { createdAt: { [Op.gte]: lastWeek } } }),
        Order.sum('finalAmount', { where: { status: 'completed' } }) || 0,
        Order.sum('finalAmount', { 
          where: { 
            status: 'completed',
            createdAt: { [Op.gte]: today }
          } 
        }) || 0,
        
        // 积分相关统计
        PointsTransaction.sum('amount', { where: { type: 'earn' } }) || 0,
        PointsTransaction.sum('amount', { where: { type: 'spend' } }) || 0,
        PointsTransaction.sum('amount', { 
          where: { 
            type: 'earn',
            createdAt: { [Op.gte]: today }
          } 
        }) || 0,
        
        // 广告相关统计
        AdView.count(),
        AdView.count({ where: { createdAt: { [Op.gte]: today } } }),
        
        // 商品相关统计
        Product.count(),
        Product.count({ where: { status: 1 } }),
        Product.count({ where: { status: 1, stock: { [Op.lte]: 10 } } }),
        
        // 邀请相关统计
        Invitation.count(),
        Invitation.count({ where: { createdAt: { [Op.gte]: today } } })
      ]);

      // 计算增长率
      const yesterdayNewUsers = await User.count({ 
        where: { 
          createdAt: { 
            [Op.gte]: yesterday,
            [Op.lt]: today
          }
        } 
      });

      const yesterdayOrders = await Order.count({ 
        where: { 
          createdAt: { 
            [Op.gte]: yesterday,
            [Op.lt]: today
          }
        } 
      });

      const yesterdayRevenue = await Order.sum('finalAmount', { 
        where: { 
          status: 'completed',
          createdAt: { 
            [Op.gte]: yesterday,
            [Op.lt]: today
          }
        } 
      }) || 0;

      return {
        users: {
          total: totalUsers,
          todayNew: todayNewUsers,
          weekNew: weekNewUsers,
          active: activeUsers,
          growthRate: yesterdayNewUsers > 0 ? 
            ((todayNewUsers - yesterdayNewUsers) / yesterdayNewUsers * 100).toFixed(2) : 0
        },
        orders: {
          total: totalOrders,
          today: todayOrders,
          week: weekOrders,
          growthRate: yesterdayOrders > 0 ? 
            ((todayOrders - yesterdayOrders) / yesterdayOrders * 100).toFixed(2) : 0
        },
        revenue: {
          total: parseFloat(totalRevenue),
          today: parseFloat(todayRevenue),
          growthRate: yesterdayRevenue > 0 ? 
            ((todayRevenue - yesterdayRevenue) / yesterdayRevenue * 100).toFixed(2) : 0
        },
        points: {
          totalIssued: Math.abs(totalPointsIssued),
          totalUsed: Math.abs(totalPointsUsed),
          todayIssued: Math.abs(todayPointsIssued),
          balance: Math.abs(totalPointsIssued) - Math.abs(totalPointsUsed)
        },
        ads: {
          totalViews: totalAdViews,
          todayViews: todayAdViews
        },
        products: {
          total: totalProducts,
          active: activeProducts,
          lowStock: lowStockProducts
        },
        invitations: {
          total: totalInvitations,
          today: todayInvitations
        }
      };
    } catch (error) {
      logger.error('Get dashboard overview failed:', error);
      throw error;
    }
  }

  /**
   * 获取实时趋势数据
   */
  async getRealTimeTrends(hours = 24) {
    try {
      const now = new Date();
      const startTime = new Date(now.getTime() - hours * 60 * 60 * 1000);

      // 按小时分组统计
      const hourlyStats = await Promise.all([
        // 用户注册趋势
        User.findAll({
          attributes: [
            [sequelize.fn('HOUR', sequelize.col('createdAt')), 'hour'],
            [sequelize.fn('COUNT', sequelize.col('id')), 'count']
          ],
          where: {
            createdAt: { [Op.gte]: startTime }
          },
          group: [sequelize.fn('HOUR', sequelize.col('createdAt'))],
          raw: true
        }),

        // 订单趋势
        Order.findAll({
          attributes: [
            [sequelize.fn('HOUR', sequelize.col('createdAt')), 'hour'],
            [sequelize.fn('COUNT', sequelize.col('id')), 'count'],
            [sequelize.fn('SUM', sequelize.col('finalAmount')), 'revenue']
          ],
          where: {
            createdAt: { [Op.gte]: startTime }
          },
          group: [sequelize.fn('HOUR', sequelize.col('createdAt'))],
          raw: true
        }),

        // 广告观看趋势
        AdView.findAll({
          attributes: [
            [sequelize.fn('HOUR', sequelize.col('createdAt')), 'hour'],
            [sequelize.fn('COUNT', sequelize.col('id')), 'count']
          ],
          where: {
            createdAt: { [Op.gte]: startTime }
          },
          group: [sequelize.fn('HOUR', sequelize.col('createdAt'))],
          raw: true
        })
      ]);

      // 生成完整的24小时数据
      const trends = [];
      for (let i = 0; i < hours; i++) {
        const hour = (now.getHours() - hours + 1 + i + 24) % 24;
        const userStat = hourlyStats[0].find(s => parseInt(s.hour) === hour);
        const orderStat = hourlyStats[1].find(s => parseInt(s.hour) === hour);
        const adStat = hourlyStats[2].find(s => parseInt(s.hour) === hour);

        trends.push({
          hour: hour.toString().padStart(2, '0') + ':00',
          users: userStat ? parseInt(userStat.count) : 0,
          orders: orderStat ? parseInt(orderStat.count) : 0,
          revenue: orderStat ? parseFloat(orderStat.revenue || 0) : 0,
          adViews: adStat ? parseInt(adStat.count) : 0
        });
      }

      return trends;
    } catch (error) {
      logger.error('Get real time trends failed:', error);
      throw error;
    }
  }

  /**
   * 获取系统健康状态
   */
  async getSystemHealth() {
    try {
      const now = new Date();
      const fiveMinutesAgo = new Date(now.getTime() - 5 * 60 * 1000);
      const oneHourAgo = new Date(now.getTime() - 60 * 60 * 1000);

      const [
        recentUsers,
        recentOrders,
        recentAdViews,
        errorOrders,
        lowStockCount,
        inactiveUsers
      ] = await Promise.all([
        User.count({ where: { createdAt: { [Op.gte]: fiveMinutesAgo } } }),
        Order.count({ where: { createdAt: { [Op.gte]: fiveMinutesAgo } } }),
        AdView.count({ where: { createdAt: { [Op.gte]: fiveMinutesAgo } } }),
        Order.count({ 
          where: { 
            status: 'cancelled',
            createdAt: { [Op.gte]: oneHourAgo }
          } 
        }),
        Product.count({ where: { status: 1, stock: { [Op.lte]: 5 } } }),
        User.count({ 
          where: { 
            lastLogin: { [Op.lt]: new Date(now.getTime() - 7 * 24 * 60 * 60 * 1000) }
          } 
        })
      ]);

      // 计算健康分数
      let healthScore = 100;
      
      // 活跃度检查
      if (recentUsers === 0 && recentOrders === 0) healthScore -= 20;
      if (recentAdViews === 0) healthScore -= 10;
      
      // 错误率检查
      if (errorOrders > 5) healthScore -= 15;
      
      // 库存检查
      if (lowStockCount > 10) healthScore -= 10;
      
      // 用户活跃度检查
      if (inactiveUsers > 1000) healthScore -= 15;

      const getHealthStatus = (score) => {
        if (score >= 90) return 'excellent';
        if (score >= 70) return 'good';
        if (score >= 50) return 'warning';
        return 'critical';
      };

      return {
        score: Math.max(0, healthScore),
        status: getHealthStatus(healthScore),
        metrics: {
          recentActivity: {
            users: recentUsers,
            orders: recentOrders,
            adViews: recentAdViews
          },
          issues: {
            errorOrders,
            lowStockCount,
            inactiveUsers
          }
        },
        alerts: [
          ...(errorOrders > 5 ? ['订单错误率过高'] : []),
          ...(lowStockCount > 10 ? ['多个商品库存不足'] : []),
          ...(recentUsers === 0 && recentOrders === 0 ? ['系统活跃度低'] : [])
        ]
      };
    } catch (error) {
      logger.error('Get system health failed:', error);
      throw error;
    }
  }

  /**
   * 获取热门数据排行
   */
  async getTopRankings() {
    try {
      const lastWeek = new Date(Date.now() - 7 * 24 * 60 * 60 * 1000);

      const [
        topUsers,
        topProducts,
        topInviters
      ] = await Promise.all([
        // 最活跃用户（按积分获得量）
        PointsTransaction.findAll({
          attributes: [
            'userId',
            [sequelize.fn('SUM', sequelize.col('amount')), 'totalPoints']
          ],
          where: {
            type: 'earn',
            createdAt: { [Op.gte]: lastWeek }
          },
          group: ['userId'],
          order: [[sequelize.fn('SUM', sequelize.col('amount')), 'DESC']],
          limit: 10,
          include: [{
            model: User,
            as: 'User',
            attributes: ['id', 'nickname', 'phone', 'avatar']
          }]
        }),

        // 热销商品
        Order.findAll({
          attributes: [
            [sequelize.literal('JSON_UNQUOTE(JSON_EXTRACT(items, "$[*].productId"))'), 'productIds'],
            [sequelize.fn('COUNT', sequelize.col('Order.id')), 'orderCount']
          ],
          where: {
            status: 'completed',
            createdAt: { [Op.gte]: lastWeek }
          },
          group: [sequelize.literal('JSON_UNQUOTE(JSON_EXTRACT(items, "$[*].productId"))')],
          order: [[sequelize.fn('COUNT', sequelize.col('Order.id')), 'DESC']],
          limit: 10,
          raw: true
        }),

        // 邀请达人
        Invitation.findAll({
          attributes: [
            'inviterId',
            [sequelize.fn('COUNT', sequelize.col('id')), 'inviteCount']
          ],
          where: {
            createdAt: { [Op.gte]: lastWeek }
          },
          group: ['inviterId'],
          order: [[sequelize.fn('COUNT', sequelize.col('id')), 'DESC']],
          limit: 10,
          include: [{
            model: User,
            as: 'inviter',
            attributes: ['id', 'nickname', 'phone', 'avatar']
          }]
        })
      ]);

      return {
        topUsers: topUsers.map(item => ({
          user: item.User,
          points: parseInt(item.dataValues.totalPoints)
        })),
        topProducts: topProducts.map(item => ({
          productId: item.productIds,
          orderCount: parseInt(item.orderCount)
        })),
        topInviters: topInviters.map(item => ({
          user: item.inviter,
          inviteCount: parseInt(item.dataValues.inviteCount)
        }))
      };
    } catch (error) {
      logger.error('Get top rankings failed:', error);
      throw error;
    }
  }

  /**
   * 获取地理分布数据
   */
  async getGeographicData() {
    try {
      // 这里可以根据用户的地理位置信息进行统计
      // 由于当前模型中没有地理位置字段，返回模拟数据
      const mockData = [
        { province: '广东', userCount: 1250, orderCount: 890 },
        { province: '北京', userCount: 980, orderCount: 720 },
        { province: '上海', userCount: 850, orderCount: 650 },
        { province: '浙江', userCount: 720, orderCount: 520 },
        { province: '江苏', userCount: 680, orderCount: 480 },
        { province: '山东', userCount: 560, orderCount: 380 },
        { province: '河南', userCount: 450, orderCount: 320 },
        { province: '湖北', userCount: 380, orderCount: 280 },
        { province: '四川', userCount: 350, orderCount: 250 },
        { province: '福建', userCount: 320, orderCount: 220 }
      ];

      return mockData;
    } catch (error) {
      logger.error('Get geographic data failed:', error);
      throw error;
    }
  }

  /**
   * 获取异常监控数据
   */
  async getAnomalyDetection() {
    try {
      const now = new Date();
      const oneHour = new Date(now.getTime() - 60 * 60 * 1000);
      const oneDay = new Date(now.getTime() - 24 * 60 * 60 * 1000);

      const [
        failedOrders,
        suspiciousUsers,
        systemErrors,
        performanceIssues
      ] = await Promise.all([
        // 失败订单
        Order.findAll({
          where: {
            status: 'cancelled',
            createdAt: { [Op.gte]: oneHour }
          },
          include: [{
            model: User,
            as: 'User',
            attributes: ['id', 'nickname', 'phone']
          }],
          limit: 10
        }),

        // 可疑用户（短时间内大量操作）
        User.findAll({
          attributes: [
            'id', 'nickname', 'phone',
            [sequelize.literal('(SELECT COUNT(*) FROM orders WHERE userId = User.id AND createdAt >= DATE_SUB(NOW(), INTERVAL 1 HOUR))'), 'recentOrders']
          ],
          having: sequelize.literal('recentOrders > 10'),
          limit: 10
        }),

        // 系统错误（这里用取消订单数量模拟）
        Order.count({
          where: {
            status: 'cancelled',
            createdAt: { [Op.gte]: oneDay }
          }
        }),

        // 性能问题（这里用长时间未完成的订单模拟）
        Order.count({
          where: {
            status: { [Op.in]: ['pending', 'processing'] },
            createdAt: { [Op.lt]: oneDay }
          }
        })
      ]);

      const anomalies = [];

      if (failedOrders.length > 5) {
        anomalies.push({
          type: 'high_failure_rate',
          severity: 'warning',
          message: `过去1小时内有${failedOrders.length}个订单失败`,
          count: failedOrders.length
        });
      }

      if (suspiciousUsers.length > 0) {
        anomalies.push({
          type: 'suspicious_activity',
          severity: 'warning',
          message: `发现${suspiciousUsers.length}个可疑用户行为`,
          count: suspiciousUsers.length
        });
      }

      if (systemErrors > 50) {
        anomalies.push({
          type: 'system_errors',
          severity: 'critical',
          message: `过去24小时系统错误数量异常：${systemErrors}`,
          count: systemErrors
        });
      }

      if (performanceIssues > 20) {
        anomalies.push({
          type: 'performance_issues',
          severity: 'warning',
          message: `发现${performanceIssues}个性能问题`,
          count: performanceIssues
        });
      }

      return {
        anomalies,
        details: {
          failedOrders: failedOrders.slice(0, 5),
          suspiciousUsers: suspiciousUsers.slice(0, 5)
        }
      };
    } catch (error) {
      logger.error('Get anomaly detection failed:', error);
      throw error;
    }
  }
}

module.exports = new DashboardService();